# coding: utf-8

import Pyro4
Pyro4.config.HMAC_KEY = "123456" # set HMAC KEY for server.py
# C:\> set PYRO_HMAC_KEY=123456 # set HMAC KEY for name server
# start pyro name server: python -m Pyro4.naming

import sqlite3
import string
from time import strptime, mktime

import config

class User():
    def __init__(self):
        pass
    
    def userRegist(self, username, password):
        conn = sqlite3.connect("sharedAgenda.db")
        cur = conn.cursor()
        
        cur.execute("SELECT username FROM cm_user WHERE username = ?", (username,))
        if not cur.fetchone():
            cur.execute("INSERT INTO cm_user VALUES (NULL, ?, ?)", (username, password))
            conn.commit()
            cur.close()
            return { "username": { "resNum": 0 } } # username result No.0
        else:
            cur.close()
            return { "username": { "resNum": 1 } } # username result No.1
        
    def userAuthentication(self, username, password):
        conn = sqlite3.connect("sharedAgenda.db")
        cur = conn.cursor()
        
        cur.execute("SELECT * FROM cm_user WHERE username = ? AND password = ?", (username, password))
        userData = cur.fetchone()
        cur.close()
        
        if not userData:
            return \
             { "userAuthentication":
               { "resNum": 0 }
             } # user authentication result No.0
        else:
            return \
             { "userAuthentication":
               { "resNum": 1, "returnVal": { "id": userData[0] } }
             } # user authentication result No.1
        
    def otherUsernameIsExist(self, otherUsername):
        conn = sqlite3.connect("sharedAgenda.db")
        cur = conn.cursor()
        
        notExistUsernameList = []
        otherUsernameList = otherUsername.split(",")
        for x in range(len(otherUsernameList)):
            otherUsernameList[x] = string.strip(otherUsernameList[x])
            username = otherUsernameList[x]
            cur.execute("SELECT * FROM cm_user WHERE username = ?", (username,))
            userData = cur.fetchone()
            
            if not userData:
                notExistUsernameList.append(username)
                
        if not notExistUsernameList:
            return \
             { "otherUsernameIsExist":
               { "resNum": 0 }
             } # other username is exist result No.0
        else:
            return \
             { "otherUsernameIsExist":
               { "resNum": 1, "returnVal": { "notExistUsernameList": notExistUsernameList } }
             } # other username is exist result No.1
             
class Meeting():
    def __init__(self):
        pass
    
    def formatedTimeToSec(self, formatedTime):
        return mktime(strptime(formatedTime, "%Y-%m-%d %H:%M:%S"))
    
    def addMeeting(self, authenticatedUser, addMeetingInputChecked):
        otherUsername = addMeetingInputChecked["otherUsername"]#"zhangqianxun, qianxun"
        start = addMeetingInputChecked["start"]#"2013-03-18 14:10:00"
        end = addMeetingInputChecked["end"]#"2013-03-18 14:40:00"#
        title = addMeetingInputChecked["title"]#"Hello! zhangqianxun & qianxun"
        
        conn = sqlite3.connect("sharedAgenda.db")
        cur = conn.cursor()
        
        otherUsernameList = otherUsername.split(",")
        
        meetingUsernameList = [authenticatedUser["username"]]
        meetingUsernameList.extend(otherUsernameList)
        
        meetingUserList = []
        
        conflictUsernameList = []
        for x in range(len(meetingUsernameList)):
            meetingUsernameList[x] = string.strip(meetingUsernameList[x])
            username = meetingUsernameList[x]
            cur.execute("SELECT id FROM cm_user WHERE username = ?", (username,))
            userData = cur.fetchone()
            
            userId = userData[0]
            meetingUserList.append([userId, username])
            cur.execute(
                "SELECT cm_meeting.start, cm_meeting.\"end\" \
                FROM link_meeting_user, cm_meeting \
                WHERE link_meeting_user.user_id = ? AND link_meeting_user.is_delete = ? \
                AND link_meeting_user.meeting_id = cm_meeting.id AND cm_meeting.is_delete = ?", (userId, config.dbIsDeleteDefault, config.dbIsDeleteDefault)
            )
            userAgendaData = cur.fetchall()
            print username, ": ", userAgendaData
            
            for userMeetingData in userAgendaData:
                userMeetingStart = userMeetingData[0]
                userMeetingEnd = userMeetingData[1]
                
                userMeetingStartSec = self.formatedTimeToSec(userMeetingStart)
                userMeetingEndSec = self.formatedTimeToSec(userMeetingEnd)
                
                startSec = self.formatedTimeToSec(start)
                endSec = self.formatedTimeToSec(end)
                
                if userMeetingStartSec <= startSec <= userMeetingEndSec or \
                    userMeetingStartSec <= endSec <= userMeetingEndSec:
                    if username not in conflictUsernameList:
                        conflictUsernameList.append(username)
                
        print conflictUsernameList
        if not conflictUsernameList:
            cur.execute("INSERT INTO cm_meeting VALUES (NULL, ?, ?, ?, ?, ?)", (authenticatedUser["id"], start, end, title, config.dbIsDeleteDefault))
            conn.commit()
            meetingId = cur.lastrowid
            
            print meetingUserList
            
            for meetingUser in meetingUserList:
                meetingUserId = meetingUser[0]
                cur.execute("INSERT INTO link_meeting_user VALUES (NULL, ?, ?, ?)", (meetingId, meetingUserId, config.dbIsDeleteDefault))
                conn.commit()
            
            cur.close()
            
            return \
             { "addMeeting":
               { "resNum": 0 }
             } # add meeting result No.0
        else:
            return \
             { "addMeeting":
               { "resNum": 1, "returnVal": { "conflictUsernameList": conflictUsernameList } }
             } # add meeting result No.1
        
    def queryMeeting(self, authenticatedUser, queryMeetingInputChecked):
        start = queryMeetingInputChecked["start"]#"2013-03-18 00:00:00"
        end = queryMeetingInputChecked["end"]#"2013-03-18 23:59:00"
        
        conn = sqlite3.connect("sharedAgenda.db")
        cur = conn.cursor()
        
        cur.execute(
            "SELECT cm_meeting.id, cm_meeting.start, cm_meeting.\"end\", cm_meeting.title, cm_user.username \
            FROM link_meeting_user, cm_meeting, cm_user \
            WHERE link_meeting_user.user_id = ? AND link_meeting_user.is_delete = ? \
            AND link_meeting_user.meeting_id = cm_meeting.id AND cm_meeting.is_delete = ? \
            AND cm_meeting.user_id = cm_user.id", (authenticatedUser["id"], config.dbIsDeleteDefault, config.dbIsDeleteDefault)
        )
        userAgendaData = cur.fetchall()
        
        queryUserAgendaList = []
        if not userAgendaData:
            return \
             { "queryMeeting":
               { "resNum": 0 }
             } # query meeting result No.0
        else:
            print userAgendaData
            
            for userMeetingData in userAgendaData:
                userMeetingStart = userMeetingData[1]
                userMeetingEnd = userMeetingData[2]
                
                userMeetingStartSec = self.formatedTimeToSec(userMeetingStart)
                userMeetingEndSec = self.formatedTimeToSec(userMeetingEnd)
                
                startSec = self.formatedTimeToSec(start)
                endSec = self.formatedTimeToSec(end)
                
                if startSec <= userMeetingStartSec <= endSec and \
                    startSec <= userMeetingEndSec <= endSec:
                    #if username not in conflictUsernameList:
                    queryUserAgendaList.append(userMeetingData)
            
            print queryUserAgendaList
            
            if not queryUserAgendaList:
                return \
                 { "queryMeeting":
                   { "resNum": 0 }
                 } # query meeting result No.0
            else:
                return \
                 { "queryMeeting":
                   { "resNum": 1, "returnVal": { "queryUserAgendaList": queryUserAgendaList } }
                 } # query meeting result No.1
        
    def deleteMeeting(self, authenticatedUser, deleteMeetingInputChecked):
        conn = sqlite3.connect("sharedAgenda.db")
        cur = conn.cursor()
        
        cur.execute(
            "SELECT user_id FROM cm_meeting \
            WHERE id = ? AND is_delete = ?", (deleteMeetingInputChecked["meetingId"], config.dbIsDeleteDefault)
        )
        meetingData = cur.fetchone()
        
        if not meetingData:
            return \
             { "deleteMeeting":
               { "resNum": 0 }
             } # delete meeting result No.0
        elif meetingData[0] != authenticatedUser["id"]:
            return \
             { "deleteMeeting":
               { "resNum": 1 }
             } # delete meeting result No.1
        else:
            meetingId = deleteMeetingInputChecked["meetingId"]
            cur.execute("UPDATE cm_meeting SET is_delete = ? WHERE id = ?", (config.dbIsDelete, meetingId))
            cur.execute("UPDATE link_meeting_user SET is_delete = ? WHERE meeting_id = ?", (config.dbIsDelete, meetingId))
            conn.commit()
            
            return \
             { "deleteMeeting":
               { "resNum": 2 }
             } # delete meeting result No.2
             
    def clearMeeting(self, authenticatedUser):
        conn = sqlite3.connect("sharedAgenda.db")
        cur = conn.cursor()
        
        cur.execute(
            "SELECT id FROM cm_meeting \
            WHERE user_id = ? AND is_delete = ?", (authenticatedUser["id"], config.dbIsDeleteDefault)
        )
        meetingsData = cur.fetchall()
        
        if not meetingsData:
            return \
             { "clearMeeting":
               { "resNum": 0 }
             } # clear meeting result No.0
        else:
            for meetingData in meetingsData:
                meetingId = meetingData[0]
                cur.execute("UPDATE cm_meeting SET is_delete = ? WHERE id = ?", (config.dbIsDelete, meetingId))
                cur.execute("UPDATE link_meeting_user SET is_delete = ? WHERE meeting_id = ?", (config.dbIsDelete, meetingId))
            
            conn.commit()
            
            return \
             { "clearMeeting":
               { "resNum": 1 }
             } # clear meeting result No.1
        
        
if __name__ == "__main__":
    user = User()
    meeting = Meeting()
    
    daemon = Pyro4.Daemon()
    ns = Pyro4.locateNS()
    serverUserURI = daemon.register(user)
    serverMeetingURI = daemon.register(meeting)
    ns.register("server.user", serverUserURI)
    ns.register("server.meeting", serverMeetingURI)
    print "Ready."
    daemon.requestLoop()
    
    